package com.example.sdbtspringboot.mapper;

import com.example.sdbtspringboot.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.DeleteMapping;

import java.util.List;

@Mapper
public interface UserMapper {

    @Insert("insert into `user`(username,password,name,phone,email,address,avatar,role)" +
            "values(#{username}, #{password}, #{name}, #{phone}, #{email}, #{address}, #{avatar}, #{role})")
    void insert(User user);

    @Update("update `user` set username = #{username},password = #{password}," +
            "name = #{name},phone = #{phone},email = #{email},address = #{address},avatar = #{avatar},role = #{role} where id =#{id}")
    void update(User user);

    @Delete("delete from `user` where id=#{id}")
    void delete(Integer id);

    @Select("select * from `user` order by id desc")//desc倒序查询 asc正序查询，默认为asc
    List<User> selectAll();

    @Select("select * from `user` where id=#{id}")
    User selectByid(Integer id);

    @Select("select * from `user` where name=#{name}")
    List<User> selectByName(String name);

    @Select("select * from `user` where name = #{name} and username = #{username}")//and表示且，or表示或。
    List<User> selectByMore(@Param("name") String name,@Param("username") String username);

    @Select("select * from `user` where name like concat('%', #{name} ,'%') or username like concat('%', #{username} ,'%')")
    List<User> selectByMo(@Param("name") String name,@Param("username") String username);

    @Select("select * from `user` where name like concat('%', #{name} ,'%') or username like concat('%', #{username}" +
            " ,'%') order by id desc limit #{skipNum},#{pagesize} ")
    List<User> selectByPage(@Param("skipNum") Integer skipNum, @Param("pagesize")Integer pagesize,
                            @Param("name") String name, @Param("username") String username);

    @Select("select count(id) from `user` " +
            "where name like concat('%', #{name} ,'%') " +
            "or username like concat('%', #{username}" +
            " ,'%') order by id desc")
    int selectCountByPage(@Param("skipNum") Integer skipNum, @Param("pagesize")Integer pagesize,
                            @Param("name") String name, @Param("username") String username);

    @Select("select * from `user` where username=#{username}")
    User selectByUserName(String username);
//    @Delete("delete from `user` where id in ()")
//    void batchDeleteUser(List<Integer> ids);
}
//"insert into `user`(username,password,name,phone,email,address,avatar)"+
//        " values(#{username},#{password},#{name},#{phone},#{email},#{address},#{avatar},)") :错误sql语句示范，多了个逗号: